package com.lmonkey.servic;

import com.lmonkey.dao.Basedao;
import com.lmonkey.shop.CART;
import com.lmonkey.shop.ORDER;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class ORDERDao {
    public static ArrayList<ORDER> selectAll(String keyword, String userid) {
        ArrayList<ORDER> list = new ArrayList<ORDER>();
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        //设置在字符集
        ResultSet rs = null;
        try {
            String sql = null;
            if (keyword != null) {
                sql = "select * from lmonkey_order where order_u_id = ? and order_p_name like ?";
                ps = conn.prepareStatement(sql);
                ps.setObject(1, userid);
                ps.setObject(2, "%" + keyword + "%");
            } else {
                sql = "select * from lmonkey_order where order_u_id = ?";
                ps = conn.prepareStatement(sql);
                ps.setObject(1, userid);
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                ORDER o = new ORDER(
                        rs.getInt("id"),
                        rs.getString("order_u_id"),
                        rs.getString("order_u_name"),
                        rs.getFloat("order_p_price"),
                        rs.getInt("order_quantity"),
                        rs.getString("order_p_name"),
                        rs.getString("time"),
                        rs.getInt("stat"),
                        rs.getString("order_p_filename"),
                        rs.getInt("order_c_id")
                );
                list.add(o);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    public static ArrayList<ORDER> selectAll1(int cp, int count, String keyword) {
        ArrayList<ORDER> list = new ArrayList<ORDER>();
        //声明结果集
        ResultSet rs = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;

        try {
            String sql = null;
            if (keyword != null) {
                sql = "select * from lmonkey_order  where order_p_name like ? or order_u_id like ? or order_u_name like ? limit ?,?";
                ps = conn.prepareStatement(sql);
                ps.setObject(1, "%" + keyword + "%");
                ps.setObject(2, "%" + keyword + "%");
                ps.setObject(3, "%" + keyword + "%");
                ps.setInt(4, count * (cp - 1));
                ps.setInt(5, count);
            } else {
                sql = "select * from lmonkey_order limit ?,?";
                ps = conn.prepareStatement(sql);
                ps.setInt(1, count * (cp - 1));
                ps.setInt(2, count);
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                ORDER o = new ORDER(
                        rs.getInt("id"),
                        rs.getString("order_u_id"),
                        rs.getString("order_u_name"),
                        rs.getFloat("order_p_price"),
                        rs.getInt("order_quantity"),
                        rs.getString("order_p_name"),
                        rs.getString("time"),
                        rs.getInt("stat"),
                        rs.getString("order_p_filename"),
                        rs.getInt("order_c_id")
                );
                list.add(o);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;

    }

    public static int[] titlepage1(int count, String keyword) {
        int arr[] = {0, 1};
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        //设置结果集
        ResultSet rs = null;
        try {
            String sql = null;
            if (keyword == null) {
                sql = "select count(*) from lmonkey_order";
                ps = conn.prepareStatement(sql);
            } else {
                sql = "select count(*) from lmonkey_order  where order_p_name like ? or order_u_id like ? or order_u_name like ?";
                ps = conn.prepareStatement(sql);
                ps.setObject(1, "%" + keyword + "%");
                ps.setObject(2, "%" + keyword + "%");
                ps.setObject(3, "%" + keyword + "%");
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                arr[0] = rs.getInt(1);
            }
            if (arr[0] % count == 0) {
                arr[1] = arr[0] / count;
            } else {
                arr[1] = (arr[0] / count) + 1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return arr;
    }

    public static ArrayList<ORDER> selectSAll(String userid) {
        ArrayList<ORDER> list = new ArrayList<ORDER>();
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        //设置在字符集
        ResultSet rs = null;
        try {
            String sql = "select * from lmonkey_order where stat = 2 and order_u_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,userid);
            rs = ps.executeQuery();
            while (rs.next()) {
                ORDER o = new ORDER(
                        rs.getInt("id"),
                        rs.getString("order_u_id"),
                        rs.getString("order_u_name"),
                        rs.getFloat("order_p_price"),
                        rs.getInt("order_quantity"),
                        rs.getString("order_p_name"),
                        rs.getString("time"),
                        rs.getInt("stat"),
                        rs.getString("order_p_filename"),
                        rs.getInt("order_c_id")
                );
                list.add(o);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    public static ArrayList<ORDER> selectYAll(String userid) {
        ArrayList<ORDER> list = new ArrayList<ORDER>();
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        //设置在字符集
        ResultSet rs = null;
        try {
            String sql = "select * from lmonkey_order where stat = 3 and order_u_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,userid);
            rs = ps.executeQuery();
            while (rs.next()) {
                ORDER y = new ORDER(
                        rs.getInt("id"),
                        rs.getString("order_u_id"),
                        rs.getString("order_u_name"),
                        rs.getFloat("order_p_price"),
                        rs.getInt("order_quantity"),
                        rs.getString("order_p_name"),
                        rs.getString("time"),
                        rs.getInt("stat"),
                        rs.getString("order_p_filename"),
                        rs.getInt("order_c_id")
                );
                list.add(y);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    public static int update(String id){
        //连接上数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try {
            String sql = "update lmonkey_order set stat = 4 where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(id));
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static ORDER selectByid(String oid){
        ORDER o = null;
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "select * from lmonkey_order where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(oid));
            rs = ps.executeQuery();
            while (rs.next()){
                o = new ORDER(
                        rs.getInt("id"),
                        rs.getString("order_u_id"),
                        rs.getString("order_u_name"),
                        rs.getFloat("order_p_price"),
                        rs.getInt("order_quantity"),
                        rs.getString("order_p_name"),
                        rs.getString("time"),
                        rs.getInt("stat"),
                        rs.getString("order_p_filename"),
                        rs.getInt("order_c_id")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return o;
    }

    public static int selectDAll(String userid) {
       int count = 0;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        //设置在字符集
        ResultSet rs = null;
        try {
            String sql = "select count(*) from lmonkey_order  where stat = 0 and order_u_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,userid);
            rs = ps.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return count;
    }

    public static int selecthAll(String userid) {
        int count = 0;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        //设置在字符集
        ResultSet rs = null;
        try {
            String sql = "select count(*) from lmonkey_order  where stat = 1 and order_u_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,userid);
            rs = ps.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return count;
    }

    public static int selectpAll(String userid) {
        int count = 0;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        //设置在字符集
        ResultSet rs = null;
        try {
            String sql = "select count(*) from lmonkey_order  where stat = 2 and order_u_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,userid);
            rs = ps.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return count;
    }

    public static int selectByid4(int id) {
        int count = 0;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        //设置在字符集
        ResultSet rs = null;
        try {
            String sql = "select count(*) from lmonkey_order  where order_c_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,id);
            rs = ps.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return count;
    }

    public static int add(Float price,int quantity,String name,String file,int cid,String uid,String uname){
        //获取当前时间
        SimpleDateFormat formatter= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = new Date(System.currentTimeMillis());
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;

        try {
            String sql = "insert into lmonkey_order values(?,?,?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,0);
            ps.setObject(2,uid);
            ps.setObject(3,uname);
            ps.setFloat(4,price);
            ps.setInt(5,quantity);
            ps.setObject(6,name);
            ps.setObject(7,formatter.format(date));
            ps.setObject(8,0);
            ps.setObject(9,file);
            ps.setInt(10,cid);
            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static int update1(String uid,String uname,int cid){
        //连接上数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;

        try {
            String sql = "update lmonkey_order set stat = 1,order_u_id = ?,order_u_name = ? where order_c_id = ? ";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,uid);
            ps.setObject(2,uname);
            ps.setObject(3,cid);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static int update2(String id){
        //连接上数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try {
            String sql = "update lmonkey_order set stat = 2 where id = ? ";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(id));
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static int update3(String id){
        //连接上数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try {
            String sql = "update lmonkey_order set stat = 1 where id = ? ";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(id));
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
